#!/bin/bash
hive -v -e "set hive.exec.mode.local.auto=true; \
insert overwrite table dm02.customer_order_summary
select
        org_name,
        cast(sum(if(code_type='取款',amt,0)) as decimal(22,6)) sum_qk,
        cast(sum(if(code_type='ATM取款',amt,0)) as decimal(22,6)) sum_aqk,
        cast(sum(if(code_type='ATM存款',amt,0)) as decimal(22,6)) sum_ack,
        cast(sum(if(code_type='存款',amt,0)) as decimal(22,6)) sum_ck,
        cast(sum(if(code_type='对公转账',amt,0)) as decimal(22,6)) sum_dgzz,
        cast(sum(if(code_type='对私转账',amt,0)) as decimal(22,6)) sum_dszz,
        cast(sum(if(code_type='大额转账',amt,0)) as decimal(22,6)) sum_dzz,
        cast(sum(if(code_type='理财',amt,0)) as decimal(22,6)) sum_lc,
        cast(sum(if(code_type='大额存款',amt,0)) as decimal(22,6)) sum_dck,
        current_timestamp etl_time
        from
(select 
        a.amt,
        if(b.org_name is not null,b.org_name,'其他渠道') org_name,
        c.code_type
        from dw02.dw_cust_info a left join 
        ods02.rdbms_org_info b on a.org_id=b.org_id
        left join ods02.rdbms_code_info c
        on a.code_id=c.code_id) a
        group by org_name;"
echo $?


